﻿CREATE PROCEDURE [ServiceEngine].[AssociateSchemaObjectToDataEntity]
@DataEntityID UNIQUEIDENTIFIER, @SchemaID UNIQUEIDENTIFIER, @Name NVARCHAR (256), @Type SMALLINT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	DECLARE @CurrentDataEntityID UNIQUEIDENTIFIER;
	
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

			-- Insert statements for procedure here
		  SELECT @CurrentDataEntityID = DataEntityID 
		  FROM [SchemaObjects] SO 
		  WHERE
				SO.SchemaID = @SchemaID AND
				SO.[SchemaObjectName] = @Name AND
				SO.[SchemaObjectType] = @Type 
				
		  IF @CurrentDataEntityID IS NULL OR @DataEntityID = @CurrentDataEntityID
		  BEGIN 
			  UPDATE 
					[SchemaObjects] 
			  SET 
					DataEntityID = @DataEntityID
			  WHERE
					SchemaID = @SchemaID AND
					[SchemaObjectName] = @Name AND
					[SchemaObjectType] = @Type 
		  END
		  ELSE
		  BEGIN
			  SELECT 
					@ErrorMessage = 'SchemaObject is already associated with a DataEntity',
					@ErrorSeverity = 11,
					@ErrorState = ERROR_STATE();

				RAISERROR (@ErrorMessage, 
						   @ErrorSeverity,
						   1);
		  END
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH
END

